package com.yachtar.property.db;

import android.database.Cursor;
import android.os.Handler;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class DBService {
	private static DBService mDBService;
	private static Object lock = new Object();
	private MySqlHelper mMySqlHelper;

	private DBService() {
		mMySqlHelper = new MySqlHelper();
	}

	public static DBService getInstance() {
		if (mDBService == null) {
			synchronized (lock) {
				if (mDBService == null) {
					mDBService = new DBService();
				}
			}
		}
		return mDBService;
	}

	public void readDatabaseIn(File dbFile, Handler handler) throws IOException {
		mMySqlHelper.readDatabaseIn(dbFile, handler);
	}

	public void writeDatabaseOut(File dbFile, Handler handler)
			throws IOException {
		mMySqlHelper.writeDatabaseOut(dbFile, handler);
	}

	public TUseApply[] getTUseApplyArr() {
		// TODO Auto-generated method stub
		try {
			if (mMySqlHelper != null && mMySqlHelper.db != null) {
				Cursor c = mMySqlHelper.db.query(TUseApply.T_Name,
						TUseApply.tUseApplyCols, null, null, null, null, null);
				if (c != null && c.moveToFirst()) {
					TUseApply[] applyArr = new TUseApply[c.getCount()];
					int i = 0;
					do {
						TUseApply apply = new TUseApply();
						apply.loadData(c);
						applyArr[i] = apply;
						i++;
					} while (c.moveToNext());
					if (c != null) {
						c.close();
					}
					return applyArr;
				}

			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return null;
	}

	public int getTotalHouseCount() {
		try {
			if (mMySqlHelper != null && mMySqlHelper.db != null) {
				Cursor c = mMySqlHelper.db.query(TUseHouse.T_Name,
						TUseHouse.tUseHouseCols, null, null, null, null, null);
				if (c != null) {
					int r = c.getCount();
					c.close();
					return r;
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return 0;
	}

	public double getTotalArea() {
		double totalArea = 0;
		try {

			if (mMySqlHelper != null && mMySqlHelper.db != null) {
				Cursor c = mMySqlHelper.db.query(TUseHouse.T_Name,
						new String[] { "sum(" + TUseHouse.C_AREA
								+ ") as total_area" }, null, null, null, null,
						null);
				if (c != null && c.moveToFirst()) {
					totalArea = c.getDouble(c.getColumnIndex("total_area"));

				}
				if (c != null) {
					c.close();
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return totalArea;
	}

	/**
	 * ��ȡ�ض�״̬�Ļ���
	 * 
	 * @param flag
	 *            ��0��ȡ�޳ɵĻ���,1��ȡ���ԵĻ���,2��ȡ��Ȩ�Ļ���,3��ȡδͶƱ�Ļ���
	 * @return
	 */
	public int getFlagHouseCount(int flag) {
		int count = 0;
		try {
			if (mMySqlHelper != null && mMySqlHelper.db != null) {
				String selection = getSelection(flag);

				Cursor c = mMySqlHelper.db.query(TUseHouse.T_Name, null,
						selection, null, null, null, null);
				if (c != null) {
					count = c.getCount();
					c.close();
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return count;
	}

	private String getSelection(int flag) {
		// TODO Auto-generated method stub
		String selection = null;
		try {
			switch (flag) {
			case 0:
				selection = TUseHouse.C_ISVOTED + "='Y' and "
						+ TUseHouse.C_TICKETFLAG + "='Y'";
				break;
			case 1:
				selection = TUseHouse.C_ISVOTED + "='Y' and "
						+ TUseHouse.C_TICKETFLAG + "='N'";
				break;
			case 2:
				selection = TUseHouse.C_ISVOTED + "='Y' and "
						+ TUseHouse.C_TICKETFLAG + "='O'";
				break;
			case 3:
				selection = TUseHouse.C_ISVOTED + "='N'";
				break;

			default:
				break;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return selection;
	}

	/**
	 * ��ȡ�ض�״̬�������
	 * 
	 * @param flag
	 *            ��0��ȡ�޳ɵĻ���,1��ȡ���ԵĻ���,2��ȡ��Ȩ�Ļ���,3��ȡδͶƱ�Ļ���
	 * @return
	 */
	public double getFlagArea(int flag) {
		double totalArea = 0;
		try {
			if (mMySqlHelper != null && mMySqlHelper.db != null) {
				String selection = getSelection(flag);
				Cursor c = mMySqlHelper.db.query(TUseHouse.T_Name,
						new String[] { "sum(" + TUseHouse.C_AREA
								+ ") as total_area" }, selection, null, null,
						null, null);
				if (c != null && c.moveToFirst()) {
					totalArea = c.getDouble(c.getColumnIndex("total_area"));
					c.close();
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return totalArea;
	}

	/**
	 * �����û������֤�ţ���ѯҵ����Ϣ
	 * 
	 * @param id
	 *            ���֤��
	 * @return
	 */
	public List<TUseHouse> queryTUseHouseByCardId(String id, String serialNo) {
		List<TUseHouse> hArr = null;
		try {
			if (mMySqlHelper != null && mMySqlHelper.db != null) {
				Cursor c = mMySqlHelper.db.query(TUseHouse.T_Name, null,
						TUseHouse.C_CARDID + " like '%" + id + "%' and "
								+ TUseHouse.C_SERIALNO + "='" + serialNo + "'",
						null, null, null, null);
				if (c != null && c.moveToFirst()) {
					hArr = new ArrayList<TUseHouse>();
					do {
						TUseHouse h = new TUseHouse();
						h.loadData(c);
						hArr.add(h);
					} while (c.moveToNext());
				}
				if (c != null) {
					c.close();
				}

			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return hArr;
	}
	
	/**
	 * ����ͶƱ״̬��ѯҵ����Ϣ
	 * 
	 * @param serialno ��Ŀ���
	 * @param isVoted ͶƱ״̬��nullΪȫ��
	 * @return
	 */
	public List<TUseHouse> getTUseHouseByVote(String serialno, Boolean isVoted) {
		List<TUseHouse> hArr = null;
		String selection = null;
		try {
			if (mMySqlHelper != null && mMySqlHelper.db != null) {
				if(isVoted == null)
					selection = "serialno='" + serialno + "'";
				else
					selection = "serialno='" + serialno + "'" + " and isVoted=" + (isVoted.booleanValue() ? "'Y'" : "'N'");
				Cursor c = mMySqlHelper.db.query(TUseHouse.T_Name, null, selection,
								null, null, null, " villageid, buildingid, cellid, cast(roomno as  integer)");
				if (c != null && c.moveToFirst()) {
					hArr = new ArrayList<TUseHouse>();
					do {
						TUseHouse h = new TUseHouse();
						h.loadData(c);
						hArr.add(h);
					} while (c.moveToNext());
				}
				if (c != null) {
					c.close();
				}
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return hArr;
	}

	/**
	 * �޸�ͶƱ��¼
	 * 
	 * @param house
	 * @return
	 */
	public boolean vote(TUseHouse house) {
		try {
			if (mMySqlHelper != null && mMySqlHelper.db != null) {
				return mMySqlHelper.db.update(TUseHouse.T_Name,
						house.createContentValues(), TUseHouse.C_GUID + "='"
								+ house.getgUID() + "'", null) > 0 ? true
						: false;

			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return false;
	}

	public HashMap<String, TUseBuild> queryBuildBySerialNO(String serialNO,
                                                           List<String> nameList) {
		HashMap<String, TUseBuild> buildMap = null;
		try {
			if (mMySqlHelper != null && mMySqlHelper.db != null) {
				Cursor c = mMySqlHelper.db.query(TUseBuild.T_Name, null,
						TUseBuild.C_SERIALNO + "='" + serialNO + "'", null,
						null, null, null);
				if (c != null && c.moveToFirst()) {
					buildMap = new HashMap<String, TUseBuild>();
					do {
						TUseBuild tUseBuild = new TUseBuild();
						tUseBuild.loadData(c);
						if (tUseBuild.getBuildingName() != null) {
							buildMap.put(tUseBuild.getBuildingName(), tUseBuild);
							if (nameList != null) {
								nameList.add(tUseBuild.getBuildingName());
							}
						}

					} while (c.moveToNext());
				}
				if (c != null)
					c.close();
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return buildMap;
	}

	public HashMap<String, TUseCell> queryCellBySerialNOAndBuildId(
            String serialNO, String buildId, List<String> nameList) {
		HashMap<String, TUseCell> cellMap = null;
		try {
			if (mMySqlHelper != null && mMySqlHelper.db != null) {
				Cursor c = mMySqlHelper.db.query(TUseCell.T_Name, null,
						TUseCell.C_SERIALNO + "='" + serialNO + "' and "
								+ TUseCell.C_BUILDINGID + "='" + buildId + "'",
						null, null, null, null);
				if (c != null && c.moveToFirst()) {
					cellMap = new HashMap<String, TUseCell>();
					do {
						TUseCell cell = new TUseCell();
						cell.loadData(c);
						if (cell.getCellName() != null) {
							cellMap.put(cell.getCellName(), cell);
							if (nameList != null) {
								nameList.add(cell.getCellName());
							}
						}

					} while (c.moveToNext());
				}
				if (c != null)
					c.close();
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return cellMap;
	}

	public HashMap<String, TUseHouse> queryHouseBySerialNoAndBuildId(
            String serialNo, String buildId, List<String> locationList) {
		HashMap<String, TUseHouse> houseMap = null;
		try {
			if (mMySqlHelper != null && mMySqlHelper.db != null) {
				Cursor c = mMySqlHelper.db
						.query(TUseHouse.T_Name, null,
								TUseHouse.C_SERIALNO + "='" + serialNo
										+ "' and " + TUseHouse.C_BUILDINGID
										+ "='" + buildId + "'", null, null,
								null, null);
				if (c != null && c.moveToFirst()) {
					houseMap = new HashMap<String, TUseHouse>();
					do {
						TUseHouse house = new TUseHouse();
						house.loadData(c);
						if (house.getLocation() != null) {
							houseMap.put(house.getLocation(), house);
							if (locationList != null) {
								locationList.add(house.getLocation());
							}
						}

					} while (c.moveToNext());
				}
				if (c != null) {
					c.close();
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return houseMap;
	}

	public HashMap<String, TUseHouse> queryHouseBySerialNoAndBuildIdAndCellId(
            String serialNo, String buildId, String cellId,
            List<String> locationList) {
		HashMap<String, TUseHouse> houseMap = null;
		try {
			if (mMySqlHelper != null && mMySqlHelper.db != null) {
				Cursor c = mMySqlHelper.db.query(TUseHouse.T_Name, null,
						TUseHouse.C_SERIALNO + "='" + serialNo + "' and "
								+ TUseHouse.C_BUILDINGID + "='" + buildId
								+ "' and " + TUseHouse.C_CELLID + "='" + cellId
								+ "'", null, null, null, null);
				if (c != null && c.moveToFirst()) {
					houseMap = new HashMap<String, TUseHouse>();
					do {
						TUseHouse house = new TUseHouse();
						house.loadData(c);
						if (house.getLocation() != null) {
							houseMap.put(house.getLocation(), house);
							if (locationList != null) {
								locationList.add(house.getLocation());
							}
						}

					} while (c.moveToNext());
				}
				if (c != null) {
					c.close();
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return houseMap;
	}

	public boolean updateHouseInfoByGUID(TUseHouse house) {
		try {
			if (mMySqlHelper != null && mMySqlHelper.db != null) {
				return mMySqlHelper.db.update(TUseHouse.T_Name,
						house.createContentValues(), TUseHouse.C_GUID + "='"
								+ house.getgUID() + "'", null) > 0 ? true
						: false;
			}

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return false;
	}
}
